package com.lxj.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.lxj.charactor.Hero;

/**
 * HeroDao
 */
public class HeroDao implements DAO {
    

    public static void main(String[] args) {
        System.out.println(Short.MIN_VALUE);
    }

    /**
     * 无参构造函数
     */
    public HeroDao() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获得数据库链接
     */
    public Connection getCon() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/how2java?charactorEncoding=utf-8", "root", "root");
    }
    /**
     * 获取数据总条数
     * @return
     */
    public int getTotal() {
        int total = 0;
        try (
            Connection c = getCon();
            Statement s = c.createStatement();
        ) {
            String sql = "select count(*) from hero";

            ResultSet rs = s.executeQuery(sql);
            if(rs.next()) {
                total = rs.getInt(1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return total;
    }


    /**
     * 添加
     */
    @Override
    public void add(Hero h) {
        String sql = "insert into hero values(null,?,?,?)";
        try (
            Connection c = getCon();
            PreparedStatement ps = c.prepareStatement(sql);
        ) {
            ps.setString(1,h.name);
            ps.setFloat(2, h.hp);
            ps.setFloat(3, h.damage);

            ps.execute();

            ResultSet rs = ps.getGeneratedKeys();
            if(rs.next()) {
                int id = rs.getInt(1);
                h.id = id;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public void delete(int id) {
        try (
            Connection c = getCon();
            Statement s = c.createStatement();
        ) {
            String sql = "delete from hero where id = " + id;
            s.execute(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void update(Hero hero) {
        String sql = "update hero set name = ?,hp = ?, damage = ? where id = ?";
        try (
            Connection c = getCon();
            PreparedStatement ps = c.prepareStatement(sql);
        ) {
            ps.setString(1, hero.name);
            ps.setFloat(2, hero.hp);
            ps.setFloat(3, hero.damage);
            ps.setInt(4, hero.id);

            ps.execute();       
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Hero get(int id) {
        Hero hero = null;
        try (
            Connection c = getCon();
            Statement s = c.createStatement();
        ) {
            String sql = "select * from hero where id = " + id;
            ResultSet rs = s.executeQuery(sql);

            if(rs.next()) {
                hero = new Hero();
                hero.id = rs.getInt("id");
                hero.hp = rs.getInt("hp");
                hero.damage = rs.getInt("damage");
                hero.name = rs.getString("name");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return hero;
    }

    @Override
    public List<Hero> list() {
        return list(0, Short.MAX_VALUE);
    }

    @Override
    public List<Hero> list(int start, int count) {
        List<Hero> list = new ArrayList<>();

        String sql = "select * from hero order by id limit ?, ?";

        try (
            Connection c = getCon();
            PreparedStatement ps = c.prepareStatement(sql);
        ) {
            ps.setInt(1, start);
            ps.setInt(2, count);
            
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Hero hero = new Hero();
                hero.id = rs.getInt("id");
                hero.name = rs.getString("name");
                hero.hp = rs.getInt("hp");
                hero.damage = rs.getInt("damage");

                list.add(hero);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}